package com.climb.moive.dao.impl;

import com.climb.moive.dao.MemberDao;
import com.climb.moive.entity.Member;
import com.climb.moive.framework.jdbc.BeanPropertyRowMapper;
import com.climb.moive.framework.jdbc.JdbcTemplate;
import com.climb.moive.framework.jdbc.RowMapper;
import com.climb.moive.util.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.List;

/**
 * @author Administrator
 */
public class MemberDaoImpl implements MemberDao {
    @Override
    public void insert(Member member) throws SQLException {
        String sql = "insert into member (account,password,name,email,phone,register_time) values (?,?,?,?,?,?)";


        Connection con = null;
        PreparedStatement ps = null;
        try {
            con = JdbcUtils.getDataSource().getConnection();
            JdbcTemplate jdbcTemplate = new JdbcTemplate(con);
            jdbcTemplate.update(sql, member.getAccount(), member.getPassword(), member.getName(), member.getEmail(), member.getPhone(), member.getRegister_time());
        } finally {
            JdbcUtils.close(ps);
            JdbcUtils.close(con);
        }

    }


    @Override
    public void update(Member member) throws SQLException {
        String sql="UPDATE `member` SET `account`=?, `password`=?, `name`=?, `email`=?, `phone`=? WHERE (`id`=?) ";
        Connection connection=null;
        try {
            connection=JdbcUtils.getDataSource().getConnection();
            JdbcTemplate jdbcTemplate=new JdbcTemplate(connection);
            jdbcTemplate.update(sql, member.getAccount(), member.getPassword(), member.getName(), member.getEmail(), member.getPhone(),member.getId());
        }  finally {
            JdbcUtils.close(connection);
        }
    }

    @Override
    public void delete(int id) throws SQLException {
        String sql="DELETE FROM `member` WHERE `id`=?";
        Connection connection=null;
        try {
            connection=JdbcUtils.getDataSource().getConnection();
            JdbcTemplate jdbcTemplate=new JdbcTemplate(connection);
            jdbcTemplate.update(sql,id);
        }  finally {
            JdbcUtils.close(connection);
        }

    }

    @Override
    public Member findById(int id) throws SQLException {
        String sql="select id,account,password,name,email,phone,register_time from member where id=?";
        Connection connection=null;
        Member member=null;
        try {
            connection=JdbcUtils.getConnection();
            JdbcTemplate jdbcTemplate=new JdbcTemplate(connection);
            RowMapper<Member> memberRowMapper=new BeanPropertyRowMapper<>(Member.class);
            member=jdbcTemplate.queryForObject(sql, memberRowMapper,id);
        } finally {
            JdbcUtils.close(connection);
        }
        return member;
    }

    @Override
    public Member findByPhone(String phone) throws SQLException {
        String sql="select * from member where phone=?";
        Connection connection=null;
        Member member=null;
        try {
            connection=JdbcUtils.getConnection();
            JdbcTemplate jdbcTemplate=new JdbcTemplate(connection);
            RowMapper<Member> memberRowMapper=new BeanPropertyRowMapper<>(Member.class);
            member=jdbcTemplate.queryForObject(sql, memberRowMapper,phone);
        } finally {
            JdbcUtils.close(connection);
        }
        return member;
    }

    @Override
    public List<Member> findAll() throws SQLException {
        String sql="select id,account,password,name,email,phone,register_time from member";
        Connection connection=null;
        List<Member> members;
        try {
            connection=JdbcUtils.getConnection();
            JdbcTemplate jdbcTemplate=new JdbcTemplate(connection);
            RowMapper<Member> memberRowMapper=new BeanPropertyRowMapper<>(Member.class);
            members = jdbcTemplate.query(sql,memberRowMapper);
        } finally {
            JdbcUtils.close(connection);
        }

        return members;
    }

    @Override
    public Member login(Member member) throws SQLException {
        String sql = "select id,account,password,name,email,phone,register_time from member where account=? and password=?";
        Connection connection=null;
        try {
            connection=JdbcUtils.getConnection();
            JdbcTemplate jdbcTemplate=new JdbcTemplate(connection);
            RowMapper<Member> memberRowMapper=new BeanPropertyRowMapper<>(Member.class);
            member = jdbcTemplate.queryForObject(sql,memberRowMapper,member.getAccount(), member.getPassword());
        } finally {
            JdbcUtils.close(connection);
        }

        return member;
    }
    /*获取普通用户总人数*/
    @Override
    public Integer count() throws SQLException {
        String sql="SELECT COUNT(1) from member";
        Connection con = null;
        Integer result=0;
        try {
            con = JdbcUtils.getConnection();
            JdbcTemplate jdbcTemplate = new JdbcTemplate(con);
            result=jdbcTemplate.queryForObject(sql,Integer.class);

        } finally {
            JdbcUtils.close(con);
        }
        return result;
    }

    @Override
    public List<Member> findMembersByName(String name) throws SQLException {
        String sql="select id,account,password,name,email,phone,register_time from member where name like ?";
        Connection connection=null;
        List<Member> members=null;
        try {
            connection=JdbcUtils.getConnection();
            JdbcTemplate jdbcTemplate=new JdbcTemplate(connection);
            RowMapper<Member> memberRowMapper=new BeanPropertyRowMapper<>(Member.class);
            members=jdbcTemplate.query(sql,memberRowMapper,"%"+name+"%");
        } finally {
            JdbcUtils.close(connection);
        }
        return members;
    }


}
